{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "outputs": [],
   "source": [
    "def line2data(line):\n",
    "    line_data = pd.Series(data=[float(i) for i in line.split(\"|\") if i != \"\"], index=['latitude', 'longitude','none0', 'none1', 'none2','none3', 'altitude', 'none5', 'norad_id', 'time', 'none8'])\n",
    "    line_data['time_format'] = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(line_data['time']))\n",
    "    return line_data\n",
    "\n",
    "def get_satellites_location(norad_id=48274):\n",
    "    try:\n",
    "        url = f\"https://www.n2yo.com/sat/instant-tracking.php?s={norad_id}&hlat=0&hlng=0&d=300&r=0&tz=GMT+08:00&O=n2yocom&callback=\"\n",
    "        request_data = requests.get(url=url)\n",
    "        if request_data.status_code == 200:\n",
    "            finaldata = pd.DataFrame(request_data.json()[0]['pos'])['d'].apply(lambda x: line2data(x))\n",
    "            finaldata['status'] = 200\n",
    "        else:\n",
    "            finaldata = pd.DataFrame({'status':[404]})\n",
    "    except Exception as e:\n",
    "        print('faild')\n",
    "        finaldata = pd.DataFrame({'status':[404]})\n",
    "\n",
    "    return finaldata"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('postgresql://localhost/huzheng')\n",
    "\n",
    "while True:\n",
    "    temp_data = get_satellites_location()\n",
    "    if temp_data['status'].unique().tolist()[0] == 200:\n",
    "        temp_data.to_sql(con=engine,name=\"css_location\", if_exists=\"replace\",index=False)\n",
    "        time.sleep(100)\n",
    "    else:\n",
    "        time.sleep(3)\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "outputs": [
    {
     "data": {
      "text/plain": "200"
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}